package jpave.annotator;

import java.sql.ResultSet;
import java.util.TreeMap;
import java.util.Vector;

import util.Debug.ErrorReport;
import util.methods.HostsCfg;
import assem.DBConn;
import jpave.database.CoreDBWrapper;

public class Web 
{
	public static void fillWebViewTable(CoreDBWrapper sqlObj, String assemblyID) 
	{
		try
		{
			ResultSet rs = null;
			DBConn db = new HostsCfg().getDBConn(sqlObj.PAVE_db);
		   	String tblName = "pv_overview";    	
	    	if (db.tableExists(tblName))
	    	{
	    		db.executeUpdate("drop table " + tblName);
	    	}		
	    	tblName = "pv_web";    	
	    	if (db.tableExists(tblName))
	    	{
	    		db.executeUpdate("drop table " + tblName);
	    	}
	    	rs = db.executeQuery("select aid from assembly where assemblyid='" + assemblyID + "'");
	    	rs.first();
	    	int AID = rs.getInt(1);
	    	
	    	// First get the library count columns
	    	String libColumns = "";
	    	String libDefCols = "";
	    	rs = db.executeQuery("select library.libid, ctglib from assemlib " + 
	    			" join library on library.LID=assemlib.LID where assemlib.aid=" + AID);
	    	while (rs.next())
	    	{
	    		String libid = rs.getString(1);
	    		int ctglib = rs.getInt("ctglib");
	    		libColumns += ",ct.L__" + libid + ",ct.LN__" + libid;
	    		libDefCols += " L__" + libid + " bigint unsigned not null, LN__" + libid + " float not null, ";
	     	}
	    	rs.close();
	    	CoreMain.PrtSpMsg(1, "\nCreating view table...");
	    	
	    	db.executeUpdate("create table " + tblName + "(" +
	    			" CTGID bigint unsigned not null, contigid varchar(30) not null, numclones int not null," +
	    			" rstat float not null, notes varchar(255), consensus_bases mediumint not null, gc_ratio float not null," +
	    			libDefCols +
	    			" totalexp bigint unsigned not null, totalexpN bigint unsigned not null, PID bigint unsigned," +
	    			" DUHID bigint unsigned, percent_id smallint unsigned default 0, " +
	    			" alignment_len int unsigned default 0, mismatches mediumint unsigned default 0, e_value double default 0," +
	    			" bit_score float default 0, blast_rank int default 0, filter_allut boolean default 0, " +
	    			"filter_noannot boolean default 1, filter_best boolean default 0, filter_species boolean default 0," +
	    			" filter_gene boolean default 0, filter_top3 boolean default 0, filter_olap boolean default 0," +
	    			"filter_ovbest boolean default 0," +
	    			" hitID varchar(30) , dbtype varchar(10), DBID int unsigned , " +
	    			"taxonomy varchar(20) , isProtein boolean default 0, nUnitranHit mediumint unsigned default 0," +
	    			" nBestHit mediumint unsigned default 0, description varchar(250), species varchar(100), " +
	    			" index(hitID), index(contigid), index(CTGID), index(description), index(species), index(DBID)," +
	    			" index(filter_best),index(filter_ovbest),index(filter_top3),index(filter_species), index(filter_gene),index(filter_olap)," +
	    			" index(filter_noannot), index(filter_allut),index(e_value)" +
	    			")");
	    	
	    	// Note that we use uniprot_id rather than hitID because of the one-time bug
	    	// where there were null hitIDs. At some point probably should go back to using hitID,
	    	// although this way is faster because of one less join.
	    	db.executeUpdate("insert into " + tblName +
	    			" (select ct.CTGID, ct.contigid, ct.numclones, " +
	    			"ct.rstat, ct.notes, ct.consensus_bases, ct.gc_ratio " 
	    			+ libColumns +
	    			" , ct.totalexp ,ct.totalexpN, uth.PID, " +
	    			"duh.DUHID, uth.percent_id," +
	    			"uth.alignment_len,uth.mismatches,uth.e_value," +
	    			" uth.bit_score, uth.blast_rank, 0 ," +
	    			" 0, uth.filter_best, uth.filter_species, " +
	    			" uth.filter_gene, uth.filter_top3, filter_olap, " +
	    			" uth.filter_ovbest," +
	 //    			" duh.hitID, " +
	    			" uth.uniprot_id,  duh.dbtype, duh.DBID, " +
	    			" duh.taxonomy, duh.isProtein, duh.nUnitranHit, " +
	    			" duh.nBestHit, duh.description, duh.species " +
	    			" from contig as ct " +
	    			" left join pja_db_unitrans_hits as uth on uth.CTGID=ct.CTGID " +
	 //    			" left join pja_db_unique_hits as duh on duh.DUHID=uth.DUHID " +
	    			" left join pja_db_unique_hits as duh on duh.hitID=uth.uniprot_id " +
	    			" where ct.aid=" + AID +
	    			" order by ct.contigid asc, uth.e_value asc, duh.hitID asc )"
	    	);
	    	rs = db.executeQuery("select count(*) from " + tblName);
	    	rs.first();
	    	int numRows = rs.getInt(1);
	    	CoreMain.PrtSpMsg(2, "Table has " + numRows + " rows.");
	       	db.executeUpdate("update " + tblName + " set filter_allut=1 where filter_best=1 or DUHID is null");
	       	db.executeUpdate("update " + tblName + " set filter_noannot=1, filter_best=0, filter_species=0,filter_gene=0, " +
	       			" filter_top3=0, filter_olap=0,filter_ovbest=0 where DUHID is null");
		}
		catch(Exception e) {ErrorReport.die(e);}
	}	
	public static void fillWebGOViewTable(CoreDBWrapper sqlObj, String assemblyID)
	{
		try
		{
			ResultSet rs = null;
			DBConn db = new HostsCfg().getDBConn(sqlObj.PAVE_db);
	    	
			String goViewTbl = "pv_go_web";    	

			System.err.println("Creating " + goViewTbl + "...");

			if (!db.tableHasColumn("assemlib","goreads"))
			{
				db.executeUpdate("alter table assemlib add goreads bigint default 0;");
			}
			if (db.tableExists(goViewTbl))
			{
				db.executeUpdate("drop table " + goViewTbl);
			}
	    	// First get the library count columns
			
	    	String libColumns = "";
	    	String libColDef = "";
	    	rs = db.executeQuery("show columns from contig like 'L\\_\\_%'"); //Mysql ignores _ here if not escaped
	    	while (rs.next())
	    	{
	    		String colName = rs.getString(1);
	    		libColumns += ",ct." + colName;
	    		libColDef += colName + " bigint unsigned not null, ";
	 
	    	}
	    	rs.close();
	       	rs = db.executeQuery("show columns from contig like 'LN\\_\\_%'");
	    	while (rs.next())
	    	{
	    		String colName = rs.getString(1);
	    		libColumns += ",ct." + colName;
	    		libColDef += colName + " float not null, ";
	    	}
	    	rs = db.executeQuery("select slims from assembly where assemblyid='" + assemblyID + "'");
	    	rs.first();
	    	String[] slims = rs.getString(1).trim().split("\\s+");
	    	String slimColDef = "";
	    	String slimIdx = "";
	    	String slimZeros = "";
			for (String slim : slims)
			{
				slim = slim.trim();
				if (slim.equals("")) continue;
				slimColDef += slim + " tinyint default 0, ";
				slimIdx += " index(" + slim + "),";
				slimZeros += ",0";
			}
	    	rs.close();
	    	
	    	// Note, must exactly match select statement below!
	    	String sql = "create table " + goViewTbl + "(" +
	    			"CTGID bigint unsigned not null, contigid varchar(20) not null, gonum int unsigned not null," +
	    			" goname varchar(255) not null, term_type varchar(55) not null, numclones int unsigned not null," +
	    			"rstat float not null, notes varchar(255), consensus_bases mediumint unsigned not null," +
	    			"gc_ratio float not null, " + libColDef + 
	    			" nUnitranHit int unsigned not null, nTotalReads bigint unsigned not null, " +
	    			"filter_best boolean not null,filter_ovbest boolean not null," +
	    			" uniprot_id varchar(30) not null, e_value double not null, " +
	    			" totalexp bigint unsigned default 0, totalexpN bigint unsigned default 0, tax_ovbest tinytext, " + slimColDef +
	    			" index(contigid), index(gonum), index(filter_best), index(filter_ovbest),index(e_value),  " +
	    			 " unique index(CTGID,gonum)" +
	    			")" ;
	    	db.executeUpdate(sql);
	    	
	    	// Now fill the table, using the sort and unique index to get the lowest-evalue 
	    	// uniprot for each go.
	    	// We'll go contig by contig as the single query is too large otherwise. 
	    	Vector<Integer> ctgs = new Vector<Integer>();
	    	rs = db.executeQuery("select ctgid from contig where assemblyid='" + assemblyID + "'");
	    	while (rs.next())
	    	{
	    		ctgs.add(rs.getInt("ctgid"));
	    	}
	    	int nCtgs = ctgs.size();
	    	for (int CTGID : ctgs)
	    	{
	    		System.err.print(nCtgs + "             \r");
	    		nCtgs--;
	           	sql = "insert ignore into  " + goViewTbl +
	    		" ( select  ct.CTGID, ct.contigid, gt.gonum, gt.descr as goname, gt.term_type," + 
	    		" ct.numclones, ct.rstat, ct.notes, ct.consensus_bases, ct.gc_ratio " + libColumns +
	    			" ,gt.nUnitranHit,gt.nTotalReads, 0, 0, uth.uniprot_id, uth.e_value, " +
	    			" 0,0,''" + slimZeros + 
	    		" from contig as ct " +
	    		" join pja_db_unitrans_hits as uth on uth.CTGID=ct.CTGID " +
	    		" join pja_uniprot_go as ug on ug.DUHID=uth.DUHID " +
	    		" join go_info as gt on gt.gonum=ug.gonum " +
	    		" where ct.CTGID=" + CTGID + " order by uth.e_value asc, uth.filter_ovbest desc )";
	           	db.executeUpdate(sql);  
	        		        	
	           	db.executeUpdate("update " + goViewTbl + " as go, pja_db_unitrans_hits as uth, pja_uniprot_go as ug " +
	        			" set go.filter_best=1 where go.CTGID=" + CTGID + " and uth.CTGID=" + CTGID + 
	        			" and ug.DUHID=uth.DUHID " +
	        			" and go.gonum=ug.gonum and uth.filter_best=1");
	           	db.executeUpdate("update " + goViewTbl + " as go, pja_db_unitrans_hits as uth, pja_uniprot_go as ug " +
	        			" set go.filter_ovbest=1,tax_ovbest=uth.taxonomy where go.CTGID=" + CTGID + " and uth.CTGID=" + CTGID + 
	        			" and ug.DUHID=uth.DUHID " +
	        			" and go.gonum=ug.gonum and uth.filter_ovbest=1");
	           	
	
	    	}
	    	System.err.println("");
	
	    	for (String slim : slims)
			{
	    		db.executeUpdate("update " + goViewTbl + " as tr,go_info as gt set tr." + slim + "=gt." + slim + " where tr.gonum=gt.gonum" );
			}
	
	    	db.executeUpdate("update " + goViewTbl + " as gv,pv_web as pv set gv.totalexp=pv.totalexp, gv.totalexpN=pv.totalexpN " +
			" where pv.CTGID=gv.CTGID");
	
	     	rs = db.executeQuery("select count(*) from " + goViewTbl);
	    	rs.first();
	    	int numRows = rs.getInt(1);
	    	System.err.println("Table has " + numRows + " rows.");		
	 
	    	// Convert the RPKM to PPMG
	    	rs = db.executeQuery("show columns from contig like 'L\\_\\_%'"); //Mysql ignores _ here if not escaped
	    	TreeMap<String,String> col2lib = new TreeMap<String,String>();
	    	while (rs.next())
	    	{
	    		String colName = rs.getString(1);
	    		String libName = colName.replace("L__", "");
	    		col2lib.put(colName, libName);
	    	}
	    	for (String colName : col2lib.keySet())
	    	{
	    		String libName = col2lib.get(colName);
	    		db.executeUpdate("update assemlib set goreads=(select sum(" + colName + ") from contig where contigid in " +
					      "  (select distinct contigid from " + goViewTbl + ")) where libid='" + libName + "'");
	    		// Here we need the goreads > 0 because we have transcript libraries in the list as well, which wind up with goreads=0. 
	    		// It would be better to not even create L__ columns for them, perhaps. 
	    		db.executeUpdate("update " + goViewTbl + ", assemlib set LN__" + libName + "= L__" + libName + "*(1000000/goreads) " +
	    				" where assemlib.libid='" + libName + "' and goreads > 0");
	    	}
		}
		catch(Exception e){ErrorReport.die(e);}
	}

}
